package com.example.demo.web.dao;

import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.web.Entity.OrganizationEntity;
import com.example.demo.web.Entity.UserEntity;
import com.example.demo.web.Entity.UserRoleUnionOrg;
import com.example.demo.web.util.XinLongUtil;

@Repository
public class  UserDAO {
	private Logger log = LogManager.getLogger(UserDAO.class);
	private JdbcTemplate jdbcTemplate;

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

    public int updateuser(UserEntity userEntity){

		// update
		String sql = "UPDATE tbl_users SET Username=?, Password=?, "
					+ "Realname=?, Sex=?, LastLoginTime=?,Status=?,Org_id=?,Createname=?,Phone=?,Idcard=?,Tryloginno=?,Authtype=?,Updatepswtime=?,Jobname=?,"
				+ "Descriptor=?,Needpwdchange=?,Tryloginno=?,Createtime=?,Updatetime=?,Updatename=? WHERE id=?";
		try{
			jdbcTemplate.update(sql, userEntity.getUsername(), userEntity.getPassword(),
					userEntity.getRealName(), userEntity.getSex(),userEntity.getLastlogintime(),
					userEntity.getStatus(),userEntity.getOrg_id(),userEntity.getCreatename(),userEntity.getPhone(),userEntity.getIdcard(),userEntity.getTryloginno(),
					userEntity.getAuthtype(),userEntity.getUpdatepswtime(),userEntity.getJobname(),userEntity.getDescriptor(),userEntity.getNeedpwdchange(),userEntity.getTryloginno(),
					userEntity.getCreatetime(),userEntity.getUpdatetime(),userEntity.getUpdatename(),userEntity.getId());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    };
    
    public int saveuser(UserEntity userEntity){    	
		// insert
		String sql = "INSERT INTO tbl_users (id,Username, Password, Realname,Sex,LastLoginTime,Status,Org_id,Createname,Phone,Idcard,Needpwdchange,Authtype,"
				+ "Updatepswtime,Jobname,Descriptor,Tryloginno,Create_check_username,Update_check_username,Updatetime,Createtime,Updatename)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			jdbcTemplate.update(sql, userEntity.getId(),userEntity.getUsername(), userEntity.getPassword(),
	    			userEntity.getRealName(), userEntity.getSex(), userEntity.getLastlogintime(),userEntity.getStatus(),userEntity.getOrg_id(),userEntity.getCreatename(),
	    			userEntity.getPhone(),userEntity.getIdcard(),userEntity.getNeedpwdchange(),userEntity.getAuthtype(),userEntity.getUpdatepswtime(),userEntity.getJobname(),userEntity.getDescriptor(),
	    			userEntity.getTryloginno(),userEntity.getCreate_check_username(),userEntity.getUpdate_check_username(),userEntity.getUpdatetime(),userEntity.getCreatetime(),userEntity.getUpdatename());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
    	return 1;
    	
    	
    };
    
    public int delete(int contactId) {
        String sql = "DELETE FROM tbl_users WHERE ID=?";
        try{
        	return jdbcTemplate.update(sql, contactId);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }
        
    }
    
    public int delete(String username) {
        String sql = "DELETE FROM tbl_users WHERE Username=?";
        try{
        	return jdbcTemplate.update(sql, username);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }        
    }
    
    public UserEntity findByUserId(String Id) throws EmptyResultDataAccessException{
    	String sql = "SELECT * FROM tbl_users WHERE ID='" + Id + "'";
        try{
        	return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(UserEntity.class));
        }catch(EmptyResultDataAccessException er){
        	return null;
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    	
    };
    
    public UserEntity findByUserName(String username) throws EmptyResultDataAccessException{
    	String sql = "SELECT us.*,rl.Rolename rolename FROM tbl_users us left join tbl_user_role ur on ur.user_id = us.id left join tbl_role rl on rl.id = ur.role_id WHERE Username='" + username + "'";
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(UserEntity.class));
    	
    }
    
    public int isExistByname(String username) {
    	String sql = "select count(*) from tbl_users where Username = '" + username + "'";
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public List<UserRoleUnionOrg> findAll(int pageNumber,int pageSize,String orgname,String username){
    	String sql = "SELECT us.id id,us.Username username,us.RealName realname,us.Sex sex,us.LastLoginTime lastlogintime,us.Status status, org.id as org_id, org.Name as org_name,us.Phone phone,us.Idcard idcard,"
    			+ "us.Authtype authtype,us.Jobname jobname,us.Descriptor descriptor FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail'";
    	if(!username.equals("")) {
    		sql += " and us.RealName = '" + username + "'";
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'";
    	}
    	
    	sql += " ORDER BY org.Orgcode, us.Username limit " + (pageNumber-1)*pageSize + "," + pageSize;
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    };
    
    public List<UserRoleUnionOrg> findAll(String orgid){
    	String sql = "SELECT us.id id,us.Username username, org.id as org_id, org.Name as org_name, us.RealName as realname"
    			+ " FROM tbl_users us,tbl_organization org WHERE us.Org_id = org.id and us.Username != 'hzwlprevail' and us.Org_id = '" + orgid + "'";  // LIMIT " + (pageNumber-1)*pageSize + "," + pageSize; 
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    };
    
    public List<UserRoleUnionOrg> findSubAll(List<OrganizationEntity> orgs,int pagenum,int pagesize,String orgname,String username){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or us.Org_id = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	
    	String sql = "SELECT us.id id,us.Username username,us.RealName realname,us.Sex sex,us.LastLoginTime lastlogintime,us.Status status, org.id as org_id, org.Name as org_name,us.Phone phone,us.Idcard idcard,"
    			+ "us.Authtype authtype,us.Jobname jobname,us.Descriptor descriptor FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail'";
    	if(!username.equals("")) {
    		sql += " and us.RealName = '" + username + "'";
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'";
    	}else {
    		sql += " and (us.Org_id = '" + sb.toString() + ")";
    	}
    	
    	sql += " ORDER BY org.Orgcode, us.Username limit " + (pagenum-1)*pagesize + "," + pagesize;
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }
    
    public int getUserCount(List<OrganizationEntity> orgs,String orgname,String username){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or Org_id = '" + org.getId() + "'");
    		}
    		i++;
    	}
    	
    	String sql = "SELECT count(*) FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail'";
    	if(!username.equals("")) {
    		sql += " and us.RealName = '" + username + "'";
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'";
    	}else {
    		sql += " and (us.Org_id = '" + sb.toString() + ")";
    	}
     
    	
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public int getUserCount(String orgname,String username){    	
    	String sql = "SELECT count(*) FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail'";
    	if(!username.equals("")) {
    		sql += " and us.RealName = '" + username + "'";
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'";
    	}
    	try{
    		return  jdbcTemplate.queryForObject(sql, Integer.class);
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}
    }
    
    public int updatetryno(UserEntity userEntity){
		// update
		String sql = "UPDATE tbl_users SET Tryloginno=? WHERE Username=?";
		try{
			return jdbcTemplate.update(sql, (userEntity.getTryloginno() + 1), userEntity.getUsername());
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }     	
    };
    
    public int updateStatus(int val,String userid){

		// update
		String sql = "UPDATE tbl_users SET Status=? WHERE id=?";
		try{
			jdbcTemplate.update(sql, val, userid);
			return 1;
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}		 
    	
    };
    
    public int setupdateNeedpwd(int val, String username){
		// update
		String sql = "UPDATE tbl_users SET Needpwdchange=? WHERE Username=?";
		try{
			return jdbcTemplate.update(sql, val, username);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }  
    	
    };
    
    public int resetpwd(String username,String defaultpsw,int flag){
		// update
		String sql = "UPDATE tbl_users SET Needpwdchange=?,Password=? WHERE Username=?";
		return jdbcTemplate.update(sql, flag,defaultpsw, username);
    	
    };
    
    public int updateRole(int roleid,String username){

		// update
		String sql = "UPDATE tbl_users SET RoleId=?,Status=? WHERE Username=?";
		return jdbcTemplate.update(sql, roleid,0, username);
    	
    }
    
    public List<UserRoleUnionOrg> findSubUsers(List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or us.Org_id = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	
    	String sql = "SELECT us.id id,us.Username username,us.RealName realname,us.Sex sex,us.LastLoginTime lastlogintime,us.Status status, org.id as org_id, org.Name as org_name,us.Phone phone,us.Idcard idcard,"
    			+ "us.Authtype authtype,us.Jobname jobname,us.Descriptor descriptor FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail' and us.Status = 0 and (us.Org_id = '" + sb.toString() + ") ORDER BY org.Orgcode, us.Username";
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }
    
    public List<UserRoleUnionOrg> findSubUsers(){
    	String sql = "SELECT us.id id,us.Username username,us.RealName realname,us.Sex sex,us.LastLoginTime lastlogintime,us.Status status, org.id as org_id, org.Name as org_name,us.Phone phone,us.Idcard idcard,"
    			+ "us.Authtype authtype,us.Jobname jobname,us.Descriptor descriptor FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail' and us.Status = 0 and ORDER BY org.Orgcode, us.Username";
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }
    
    public List<UserRoleUnionOrg> findUsersByOrgid(String orgid){
    	
    	String sql = "SELECT us.id id,us.Username username,us.RealName realname,us.Sex sex,us.LastLoginTime lastlogintime,us.Status status, org.id as org_id, org.Name as org_name,us.Phone phone,us.Idcard idcard,"
    			+ "us.Authtype authtype,us.Jobname jobname,us.Descriptor descriptor FROM tbl_users us left join tbl_organization org on us.Org_id = org.id WHERE us.Username != 'hzwlprevail' and us.Org_id = '" + orgid + "' and us.Status = 0 ORDER BY us.Username";
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }
    
    public List<UserRoleUnionOrg> findSubJinKuUsers(List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or us.Org_id = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT us.id,us.Username as username,us.RealName as realname,org.Name as org_name,rl.Rolename as rolename,rl.id as roleid FROM tbl_user_role ur left join tbl_users us on us.id = ur.user_id left join tbl_role rl on rl.id = ur.role_id left join tbl_organization org on org.id = us.Org_id where (ur.role_id = '727a4c59b9054e8e9324b201bb19c1a6' or ur.role_id = '49c75a023afd4e608ab0bf82f3441dcd') and us.Username != 'hzwlprevail' and (us.Org_id = '" + sb.toString() + ") and us.Status = 0  ORDER BY org.Orgcode, us.Username";
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }
    
    public List<UserRoleUnionOrg> findJinKuUsersByOrgid(String orgid){
    	
    	String sql = "SELECT us.id,us.Username as username,us.RealName as realname,org.Name as org_name,rl.Rolename as rolename,rl.id as roleid FROM tbl_user_role ur left join tbl_users us on us.id = ur.user_id left join tbl_role rl on rl.id = ur.role_id left join tbl_organization org on org.id = us.Org_id where (ur.role_id = '727a4c59b9054e8e9324b201bb19c1a6' or ur.role_id = '49c75a023afd4e608ab0bf82f3441dcd') and us.Username != 'hzwlprevail' and us.Org_id = '" + orgid + "' and us.Status = 0  ORDER BY org.Orgcode, us.Username";
     
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }

    public UserRoleUnionOrg findUserByName(String username) {
    	String sql = "SELECT us.id id,us.Username username,us.RealName realname,us.Sex sex,us.LastLoginTime lastlogintime,us.Status status, org.id as org_id, org.Name as org_name,us.Phone phone,us.Idcard idcard,"
    			+ "us.Authtype authtype,us.Jobname jobname,us.Descriptor descriptor FROM tbl_users us,tbl_organization org WHERE us.Org_id = org.id and us.Username != 'hzwlprevail' and us.Username = '" + username + "'";
    	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(UserRoleUnionOrg.class));
    }
    
    public String findRolenameByUserId(String Id){
    	String sql = "SELECT role.Rolename FROM tbl_role role,tbl_user_role ur WHERE ur.user_id='" + Id + "' and ur.role_id = role.id";
        try{
        	return jdbcTemplate.queryForObject(sql, String.class);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    	
    }
    
    public int findRoleByUsername(String username) {
    	String sql = "SELECT ur.role_id FROM tbl_users us,tbl_user_role ur WHERE us.Username='" + username + "' and ur.user_id = us.id";
    	try{
        	String roleid = jdbcTemplate.queryForObject(sql, String.class);
        	if(roleid.equals("0d649ac205dc4276abb075d079085ad7")) {
        		//主管
        		return 3;
        	}else if(roleid.equals("1e49be5007c545498f2b57796f3058a7")) {
        		//日常管理员
        		return 2;
        	}else if(roleid.equals("49c75a023afd4e608ab0bf82f3441dcd")) {
        		//金库经办
        		return 6;
        	}else if(roleid.equals("5a4c57f045a44c4f98acbb487c8d9eeb")) {
        		//柜员
        		return 4;
        	}else if(roleid.equals("727a4c59b9054e8e9324b201bb19c1a6")) {
        		//金库管理员
        		return 5;
        	}else if(roleid.equals("d3580565f3804d5ea72e4c74a7e714f5")) {
        		//系统管理员
        		return 1;
        	}else if(roleid.equals("1e49be5007c545498f2b57796f3058a8")) {
        		//调拨管理员
        		return 7;
        	}
        	return 99;
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 99;
        }
    }
    
    public boolean isRootOrg(String orgid) {
    	if(orgid.equals("25f22a3b168645e9b653dacc27775849")) {
    		return true;
    	}else {
    		return false;
    	}
    }
    
    /*===================================tbl_organization===============================================*/
    public List<OrganizationEntity> findSubOrg(String parentid) throws EmptyResultDataAccessException{
    	//String sql = "SELECT id FROM (SELECT * FROM tbl_organization WHERE Parentid IS NOT NULL) rd,(SELECT @pid := '" + parentid + "') pd" +
    	//		" where FIND_IN_SET( Parentid, @pid ) > 0 AND @pid := concat( @pid, ',', id )"; 
    	
    	String sql = "SELECT DATA.* FROM(SELECT @ids as _ids,( SELECT @ids := GROUP_CONCAT(id) FROM tbl_organization WHERE FIND_IN_SET(Parentid, @ids)) as cids," + 
			"@l := @l+1 as level FROM tbl_organization, (SELECT @ids :='" + parentid + "', @l := 0 ) b WHERE @ids IS NOT NULL" + 
			") id, tbl_organization DATA WHERE FIND_IN_SET(DATA.id, ID._ids)";
    	try{
    		return  jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OrganizationEntity.class));
    	}catch(Exception e){
    		return null;
    	}
    	
    };

}

